<?php
namespace App\Models;

use DB;
use Uuid;
use App\Common\tbCommon;
use Illuminate\Database\Eloquent\Model;

class CommentM extends Model
{
    public static  $STable="lee_icon_comment";
    protected  $table="lee_icon_comment";

    
    /**
     * 根据 icon的fid 返回 所有comment的uuid
     * @param bigint $icnfid
     * @return comment 的id集合
     * likes: [comment1,comment2,comment3]
     */
    static public function getCmtIds($icnfid){
        $cmtT=self::$STable;
        $sql="select cmt.id as cmtid from $cmtT as cmt
        where cmt.iconfid =$icnfid";
        $cmtIds=DB::select($sql);
        return array_column($cmtIds, 'cmtid');
    }
    
    /**
     * 添加单条记录
     * @param array $data 输入数据
     * 如 ['comment'=>'','userid'=>887,'iconfid'=>123]
     */
    static public function addItem(array $data){
        $commentInst=new CommentM();
        foreach ($data as $key => $value){
            $commentInst->$key=$value;
        }
        // 初始化 被点赞数
        //$commentInst->beliked=0;
        $commentInst->save();
        return $commentInst->id;
        
    }
    
    /**
     * 添加多条数据记录
     * @param array $datas,输入数据 二维数组
     * 如 [[userid'=>455,'comment'=>comment1,'iconfid'=>125],
     * [userid'=>4545,'comment'=>comment2,'iconfid'=>1325]]
     */
    static public function addItems(array $datas){
        $tbName=self::$STable;
        $newDatas=tbCommon::addMissColumnValue($tbName, $datas);
        // 批量插入数据
        DB::table($tbName)->insert($newDatas);
    }
    
    /**
     * 根据comment的id删除 该记录
     * @param bigint $cmtid，comment's id
     */
    static public function deltItem($cmtid){
        self::where('id',$cmtid)->delete();
    }
    
    /**
     * 根据icon的fid值 删除数据 记录
     * @param bigint $icnfid ,icon's fid
     */
    static public function deltItemByIcnFid($icnfid){
        self::where('iconfid',$icnfid)->delete();
    }
    
    /**
     * 依据iconfid 返回 icon的评论数
     * @param bigint $iconfid, icon的fid值
     */
    static public function singleCounts($iconfid){
        return CommentM::where('iconfid','=',$iconfid)->count();
    }
    
    /**
     * 依据 comment的id,添加 被点赞数
     * @param bigint $cmtid，comment的id
     */
    static public function plusFavour($cmtid){
       self::where('id',$cmtid)->increment('beliked',1);
    }
    
    /**
     * 依据 comment的id,减少 被点赞数
     * @param bigint $cmtid，comment的id
     */
    static public function minFavour($cmtid){
        self::where('id',$cmtid)->decrement('beliked',1);
    }
    
    /**
     * 自增回复数
     * @param bigint $cmtid,comment的id
     */
    static  public function plusRpNum($cmtid){
        self::where('id',$cmtid)->increment('rpnum',1);
    }
    /**
     * 自减更新回复数
     * @param bigint $cmtid,comment的id
     */
    static  public function minRpNum($cmtid){
       self::where('id',$cmtid)->decrement('rpnum',1);
    }
    /**
     * 依据icon fid的集合 返回 icon的评论数
     * @param array $iconfids, icon的fid的集合
     * 如: [iconfid1,iconfid2,iconfid3]
     * 返回形式 : [
     *      ['icnfid'=>icnfid1,'cntnum'=>num1],
     *      ['icnfid'=>icnfid2,'cntnum'=>num2],
     *      ['icnfid'=>icnfid3,'cntnum'=>num4],
     *   ]
     */
    static public function multiCounts(array $iconfids){
        $cmtT=self::$STable;
        $rpT=ReplyM::$STable;
        $icStr=implode(',', $iconfids);
        $sql="select ct.iconfid as icnfid,count (ct.id) as cmtnum  
            from $cmtT as ct 
            left outer join $rpT as rp
            on ct.id=rp.commentid           
            where ct.iconfid in ($icStr)           
            group by ct.iconfid";
        return DB::select($sql);
    }
    
    /**
     * 依据 Icon的fid返回其 评论及其回复
     * @param bigint $iconfid,icon的fid值
     * 返回类型数据说明:
     * cmtid（comment的id）,comment（comment内容）,
     * cmt_userid（评论用户的id）,cmttime（评论时间）,
     * rpcontent（回复内容）,rp_userid（回复者id）,
     * be_rp_userid（被回复者id）,rptime（回复时间）
     */
    static public function getCmtRps($iconfid){
        $cmtT=self::$STable;
        $rpT=ReplyM::$STable;
        $sql="select cmt.id as cmtid, cmt.comment as comment,
             cmt.userid as cmt_userid, cmt.created_at as cmttime,
            rp.rpcontent as rpcontent,rp.userid as rp_userid,
            rp.rp_userid as be_rp_userid,rp.updated_at as rptime,rp.id as rpid
            from $cmtT as cmt
            left outer join $rpT as rp
            on cmt.id=rp.commentid
            where cmt.iconfid=$iconfid";
        return DB::select($sql);
    }
    
    /**
     * 根据icon的fid和user的id，返回指定数目的记录。
     * @param bigint $iconfid,icon的fid值
     * @param bigint $userid，user的id值
     * @param int $limit,返回记录限制数目
     * @param int $offset,记录的偏移量
     * 
     * 返回的记录：
     * cmtid（comment的id）,comment（评论内容）,createdat（评论创建时间）,
     * rpnum（回复数）,cmtfavour（标记该评论是否被检索的user点赞）,
     * userid（用户id）,username（用户名）,avatar（头像）
     */
    static public function loadCmts($iconfid,$userid,$limit=10,$offset=0){
        $cmtT=self::$STable;
        //$rpT=ReplyM::$STable;
        $usT=UserM::$STable;
        $cmtfT=CommentFavourM::$STable;
        $sql="select tm.cmtid,tm.comment,tm.createdat,tm.rpnum,tm.likednum,
            us.id as userid ,us.name as username ,us.avatar as avatar,
            cmtf.id as cmtfavour
            from 
             (select cmt.id as cmtid,cmt.comment as comment,
                cmt.userid as userid,
                cmt.created_at as createdat,        
                cmt.rpnum as rpnum , 
                cmt.beliked as likednum
                from $cmtT as cmt      
                
                where cmt.iconfid=$iconfid
                limit $limit offset $offset ) as tm
            left  outer join $usT  as us 
            on tm.userid=us.id
        left outer join $cmtfT as cmtf
        on tm.cmtid=cmtf.commentid and cmtf.userid=$userid";
        return DB::select($sql);
    }
    
    /***
     * 依据 点赞数 评论数 创建时间  分页加载 排序的评论
     * 以返回最新评论为 次要基准
     * @param unknown $iconfid
     * @param unknown $userid
     * @param number $limit
     * @param number $offset
     */
    static public function loadNewestCmtByPage($iconfid,$userid,$limit=10){
        $cmtT=self::$STable;
        $usT=UserM::$STable;
        $cmtfT=CommentFavourM::$STable;
        $sql=
        "select tm.cmtid,tm.comment,tm.createdat,tm.rpnum,tm.likednum,
        us.id as userid ,us.name as username ,us.avatar as avatar,
        cmtf.id as cmtfavour
        from
        (select cmt.id as cmtid,cmt.comment as comment,
        cmt.userid as userid,
        cmt.created_at as createdat,
        cmt.rpnum as rpnum,
        cmt.beliked as likednum
        from $cmtT as cmt
        where cmt.iconfid=$iconfid 
        order by cmt.beliked DESC, cmt.rpnum DESC,cmt.id DESC  
        limit $limit offset 0  ) as tm
        left  outer join $usT  as us
        on tm.userid=us.id
        left outer join $cmtfT as cmtf
        on tm.cmtid=cmtf.commentid and cmtf.userid=$userid";
        return DB::select($sql);
    }
    
    /**
     * 依据comment的fid值 加载
     * 小于该fid值的限制记录
     * @param unknown $iconfid
     * @param unknown $userid
     * @param unknown $cmtfid
     * @param number $limit
     */
    static public function loadCmtByMinFid($iconfid,$userid,$cmtfid,$limit=10){
        $cmtT=self::$STable;
        $usT=UserM::$STable;
        $cmtfT=CommentFavourM::$STable;
         $sql=
        "select tm.cmtid,tm.comment,tm.createdat,tm.rpnum,tm.likednum,
        us.id as userid ,us.name as username ,us.avatar as avatar,
        cmtf.id as cmtfavour
        from
        (select cmt.id as cmtid,cmt.comment as comment,
        cmt.userid as userid,
        cmt.created_at as createdat,
        cmt.rpnum as rpnum,
        cmt.beliked as likednum
        from $cmtT as cmt
        where cmt.iconfid=$iconfid and cmt.id < $cmtfid
        order by cmt.beliked DESC, cmt.rpnum DESC,cmt.id DESC  
        limit $limit offset 0  ) as tm
        left  outer join $usT  as us
        on tm.userid=us.id
        left outer join $cmtfT as cmtf
        on tm.cmtid=cmtf.commentid and cmtf.userid=$userid";
        return DB::select($sql);
    }
    
    
}

?>